-- 1. create table
CREATE TABLE employee (emp_id NUMBER PRIMARY KEY,emp_name VARCHAR2(100) NOT NULL,emp_salary NUMBER(10,2),emp_position VARCHAR2(50),hire_date DATE,department_id NUMBER);
-- 2. 测试重命名
ALTER TABLE employee RENAME TO employee_new;
-- 3. 测试删除字段emp_position
ALTER TABLE employee_new DROP COLUMN emp_position;
-- 4. 测试新增字段emp_email
ALTER TABLE employee_new ADD emp_email VARCHAR2(100);
-- 5. 测试修改字段精度emp_salary
ALTER TABLE employee_new MODIFY emp_salary NUMBER(12, 2);
-- 6. 测试字段重命名emp_email到employee_email
ALTER TABLE employee_new RENAME COLUMN emp_email TO employee_email;
-- 7. 测试数据插入
INSERT INTO employee_new (emp_id, emp_name, emp_salary, hire_date, department_id, employee_email) VALUES (1, '张三', 5000.00, TO_DATE('2025-03-24', 'YYYY-MM-DD'), 101, 'zhangsan@example.com');
INSERT INTO employee_new (emp_id, emp_name, emp_salary, hire_date, department_id, employee_email) VALUES (2, '李四', 5000.00, TO_DATE('2025-03-24', 'YYYY-MM-DD'), 101, 'zhangsan@example.com');
COMMIT;
-- 8. 测试删除数据
DELETE FROM employee_new WHERE emp_id = 2;
-- 9. 测试删除测试表
DROP TABLE EMPLOYEE_NEW;
-- 10. 测试创建RENAME表名
CREATE TABLE "rename" (id NUMBER PRIMARY KEY,name VARCHAR2(100),created_at DATE DEFAULT SYSDATE);
-- 11. 测试表名RENAME操作
ALTER TABLE "rename" RENAME TO "rename_new_table";
-- 12. 测试删除RENAME表名
DROP TABLE "rename_new_table";
-- 13. 测试创建TRUNCATE表名
CREATE TABLE truncate11 (col1 varchar(1024),col2 integer);
-- 14. 测试Truncate表操作
truncate TABLE truncate11;
-- 15. 测试删除Truncate表名
DROP TABLE truncate11;
COMMIT;
-- 16. 测试创建全表名带双引号表
CREATE TABLE "CAPTBOX"."FULLTESTA" ("ID" VARCHAR2(64) PRIMARY KEY, "TITLE" VARCHAR2(100) NOT NULL, "AUTHOR" VARCHAR2(100) NOT NULL, "WEBSITE" VARCHAR2(100) NOT NULL, "UNIVERSITY" VARCHAR2(100) NOT NULL);
-- 17. 测试删除全表名带双引号表
DROP TABLE "CAPTBOX"."FULLTESTA";
-- 18. 测试创建新增字段表
CREATE TABLE ziduan (id NUMBER PRIMARY KEY,emp_salary NUMBER(10),emp_position VARCHAR2(50),department_id NUMBER);
-- 19. 测试新增字段
ALTER TABLE ziduan ADD emp_email VARCHAR2(100);
-- 20. 测试新增多个字段
ALTER TABLE ziduan ADD (emp_phone VARCHAR2(15),rmpaddress VARCHAR2(255),rmp_status VARCHAR2(20));
-- 21. 测试删除单个字段
ALTER TABLE ziduan DROP (emp_phone);
-- 22. 测试删除多个字段
ALTER TABLE ziduan DROP (emp_position);
-- 23. 测试创建字段重命名表
CREATE TABLE departments (dept_id NUMBER PRIMARY KEY,dept_name VARCHAR2(100),location VARCHAR2(100),last_name VARCHAR2(50));
-- 24. 测试字段重命名
ALTER TABLE departments RENAME COLUMN dept_name TO department_name;
-- 25. 测试创建单字段主键约束表(测试表有主键_一个字段)
CREATE TABLE "单主键表asd" ("主键列aaa" NUMBER PRIMARY KEY,"普通列bbb" VARCHAR2(100));
-- 26. 测试创建复合主键表(测试表有主键_多个字段)
CREATE TABLE "复合主键表" ("主键1" NUMBER,"主键2" VARCHAR2(50),"普通列" DATE,PRIMARY KEY ("主键1", "主键2"));
-- 27. 测试建立表无主键有唯一建_单个字段
CREATE TABLE "单唯一键表" ("唯一列" VARCHAR2(50) UNIQUE,"普通列" NUMBER);
-- 28. 测试表无主键有唯一键_多个字段
CREATE TABLE "复合唯一键表" ("列1" VARCHAR2(50),"列2" NUMBER,UNIQUE ("列1", "列2"));
-- 29. 测试无主键无唯一键_有唯一字段组合
CREATE TABLE "组合唯一表" ("字段1" NUMBER,"字段2" VARCHAR2(50),"字段3" DATE);
-- 30. 测试无主键无唯一键_无唯一标识
CREATE TABLE "无约束表" ("列1" NUMBER,"列2" VARCHAR2(100));
-- 31. 测试无主键，多个唯一约束（单字段+复合字段）
CREATE TABLE "无主键多唯一" ("唯一列_编号#2024" NUMBER UNIQUE,"列_邮箱" VARCHAR2(100),"列_手机" VARCHAR2(20),"备注" VARCHAR2(200) NULL,UNIQUE ("列_邮箱", "列_手机"));
-- 32. 测试字符串类型主键-长度100
CREATE TABLE "主键表_字符串长度100" ("主键列_编号#100" VARCHAR2(100) PRIMARY KEY,"列_描述" VARCHAR2(200),"创建时间" DATE DEFAULT SYSDATE);
-- 33. 测试字符串主键长度500
CREATE TABLE "主键表_字符串长度500" ("主键列_超长ID@500" VARCHAR2(500) PRIMARY KEY,"列_描述" NVARCHAR2(1000),"记录时间" TIMESTAMP(6) DEFAULT SYSTIMESTAMP);
-- 34. 测试中文+符号+数字的唯一键
CREATE TABLE "唯一键表_中文唯一键" ("唯一列_业务编号" VARCHAR2(100) UNIQUE,"列_描述" CLOB,"创建人" VARCHAR2(30));
-- 35. 测试源表无主键仅有一个唯一键
CREATE TABLE orders_source (order_code VARCHAR2(20) UNIQUE NOT NULL,customer VARCHAR2(50),amount NUMBER(10,2),order_date DATE DEFAULT SYSDATE);
-- 36. DEFAULT表测试
CREATE TABLE FAM_FIXED_ASSERTS66XX ("ID" VARCHAR2(32) NOT NULL ENABLE,"ASSERT_CODE" VARCHAR2(20) NOT NULL ENABLE,"ASSERT_NAME" VARCHAR2(100) NOT NULL ENABLE,"EQUIPMENT_TYPE" VARCHAR2(1) NOT NULL ENABLE,"ASSERT_TYPE" VARCHAR2(1) NOT NULL ENABLE,"ON_OR_OFF_TYPE" VARCHAR2(1) NOT NULL ENABLE,"HOST_SYS_NAME" VARCHAR2(50),"ASSERT_STATUS" VARCHAR2(1) DEFAULT '1' NOT NULL ENABLE,"ASSERT_LOCATION" VARCHAR2(50) DEFAULT '库存' NOT NULL ENABLE,"NETWORK_LOCATION" VARCHAR2(1),"REMARK" VARCHAR2(255),"PURCHASE_DATE" DATE NOT NULL ENABLE,"IS_XINCHUANG" VARCHAR2(1),"IS_CANNOT_XINCHUANG" VARCHAR2(1),"PLAN_XINCHUANG" DATE,"PLAN_DISPOSAL" VARCHAR2(20),"DEPARTMENT_CODE" VARCHAR2(1) DEFAULT '6',"USE_PERSON_NAME" VARCHAR2(20),"COUTBOUND_TIME" DATE,"SCRAPPED_TIME" DATE,"UPDATE_TIME" DATE NOT NULL ENABLE,"ORIGINAL_ASSERT_CODE" VARCHAR2(32),CONSTRAINT "PK_FAM_FIXED_ASSERTS6" PRIMARY KEY ("ID"));
-- 37. 测试DEFAULT表的-INSERT
INSERT INTO FAM_FIXED_ASSERTS66xx ("ID", "ASSERT_CODE", "ASSERT_NAME", "EQUIPMENT_TYPE", "ASSERT_TYPE", "ON_OR_OFF_TYPE", "HOST_SYS_NAME", "ASSERT_STATUS", "ASSERT_LOCATION", "NETWORK_LOCATION", "REMARK", "PURCHASE_DATE", "IS_XINCHUANG", "IS_CANNOT_XINCHUANG", "PLAN_XINCHUANG", "PLAN_DISPOSAL", "DEPARTMENT_CODE", "USE_PERSON_NAME", "COUTBOUND_TIME", "SCRAPPED_TIME", "UPDATE_TIME", "ORIGINAL_ASSERT_CODE") VALUES ('2ab133bade1ebb70e063144e080aaa8f', '104000000112', 'ThinkPad E490', '1', '2', '1', 'win10', '1', '数字化管理部', NULL, '计划处置 库存-用友测试', TO_DATE('2019-12-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '0', '0', NULL, NULL, '6', '数字化管理部', NULL, NULL, TO_DATE('2025-01-02 17:56:17', 'SYYYY-MM-DD HH24:MI:SS'), NULL);
-- 38. 测试DEFAULT-INSERT
INSERT INTO "FAM_FIXED_ASSERTS66XX"("ID", "ASSERT_CODE", "ASSERT_NAME", "EQUIPMENT_TYPE", "ASSERT_TYPE", "ON_OR_OFF_TYPE", "HOST_SYS_NAME", "ASSERT_STATUS", "ASSERT_LOCATION", "NETWORK_LOCATION", "REMARK", "PURCHASE_DATE", "IS_XINCHUANG", "IS_CANNOT_XINCHUANG", "PLAN_XINCHUANG", "PLAN_DISPOSAL", "DEPARTMENT_CODE", "USE_PERSON_NAME", "COUTBOUND_TIME", "SCRAPPED_TIME", "UPDATE_TIME", "ORIGINAL_ASSERT_CODE") VALUES ('2ab133baddcbbb70e063144e080aaa8f', '104000000137', '联想AI0520', '1', '3', '1', 'win10', '2', '监管网络公共区', '3', NULL, TO_DATE('2021-12-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '0', '1', NULL, NULL, NULL, '综合管理部', NULL, NULL, TO_DATE('2025-01-02 17:56:17', 'SYYYY-MM-DD HH24:MI:SS'), NULL);
-- 39. 测试中文表+特殊字符表创建
CREATE TABLE "测试表_!@#$%^&*+=-2025()" ("列1#特殊" NUMBER ,"column!2" VARCHAR2(20) ,"3rd列" DATE);
-- 40. 测试删除特殊字符中文表
DROP TABLE "测试表_!@#$%^&*+=-2025()";
-- 41. 测试中文字段表创建
CREATE TABLE "学生" ("学号" NUMBER PRIMARY KEY,"姓名" VARCHAR2(100) NOT NULL,"年龄" NUMBER);
-- 42. 测试插入中文表名数据
INSERT INTO 学生 (学号, 姓名, 年龄) VALUES (1, '张一', 20);  
INSERT INTO 学生 (学号, 姓名, 年龄) VALUES (2, '李二', 22);
INSERT INTO 学生 (学号, 姓名, 年龄) VALUES (3, '李三', 23);
INSERT INTO 学生 (学号, 姓名, 年龄) VALUES (4, '李四', 24);
INSERT INTO 学生 (学号, 姓名, 年龄) VALUES (5, '李五', 25);
COMMIT;
-- 43. 测试新增中文字段名
ALTER TABLE 学生 ADD 家庭地址 VARCHAR2(100);
INSERT INTO 学生 (学号, 姓名, 年龄, 家庭地址) VALUES (991, '张一', 20, '上海三林浦东软件园懿德路219号1号楼');
-- 44. 测试删除中文表
DROP TABLE "学生";
-- 45. 测试创建中文表名最大长度10中文字
CREATE TABLE "一二三四五六七八九十" ("学号" NUMBER PRIMARY KEY,"姓名" VARCHAR2(100) NOT NULL,"年龄" NUMBER);
-- 46.测试插入中文表名最大长度
INSERT INTO "一二三四五六七八九十" ("学号", "姓名", "年龄") VALUES (123, 'UNQ_001', 123);
-- 47.测试删除表名最大长度10中文字
DROP TABLE "一二三四五六七八九十";
-- 48.测试创建包含大写主键表
CREATE TABLE simple_users (user_code VARCHAR2(10) PRIMARY KEY,user_name VARCHAR2(30),age NUMBER(3));
-- 49.测试插入全大写主键数据
INSERT INTO simple_users (user_code, user_name, age) VALUES ('USER001', 'JOHN DOE', 28);
INSERT INTO simple_users VALUES ('ADMIN', 'SYSTEM ADMINISTRATOR', 35);
COMMIT;
-- 50.测试创小写字段名主键表
CREATE TABLE simple_users_smale (user_code VARCHAR2(10) PRIMARY KEY,user_name VARCHAR2(30),age NUMBER(3));
-- 51.测试插入全小写主键数据
INSERT INTO simple_users_smale (user_code, user_name, age) VALUES ('user001', 'john doe', 28);
INSERT INTO simple_users_smale VALUES ('admin', 'system administrator', 35);
COMMIT;
-- 52.创建表（时间字符串主键
CREATE TABLE time_records (time_key VARCHAR2(15) PRIMARY KEY,event_name VARCHAR2(50));
-- 53.测试插入时间数据
INSERT INTO time_records VALUES (TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS'), '系统启动');
-- 54.测试创建联合唯一键表
CREATE TABLE multi_uk (id int,id2 int，val int，UNIQUE (id, id2));
-- 55.测试创建NOT NULL约束表
CREATE TABLE example (id NUMBER NOT NULL,name VARCHAR2(100) NOT NULL);
-- 56.测试创建UNIQUE约束表
CREATE TABLE "UNIQUE" (id NUMBER UNIQUE,email VARCHAR2(100) UNIQUE);
-- 57.测试创建PRIMARY KEY约束表
CREATE TABLE "PRIMARY" (id NUMBER PRIMARY KEY,name VARCHAR2(100));
-- 58.测试创建CHECK约束表
CREATE TABLE "CHECK" (id NUMBER,age NUMBER CHECK (age >= 18));
-- 59.测试创建DEFAULT约束表
CREATE TABLE "DEFAULT" (id NUMBER,created_date DATE DEFAULT SYSDATE);
-- 60.测试创建UNIQUE约束表
CREATE TABLE "multi_uk_test2" (id int,id2 int,val int,UNIQUE (id, id2));
-- 61.测试Nologging表被忽略
CREATE TABLE my_nologging_table (id NUMBER,name VARCHAR2(50),description VARCHAR2(200)) NOLOGGING;
-- 62.测试创建中文表名-中文字段数据表
CREATE TABLE "单条数据测试表" ("记录ID" NUMBER PRIMARY KEY,"姓名" VARCHAR2(50) NOT NULL,"年龄" NUMBER(3));
-- 63.测试中文表名-中文字段,数据插入
INSERT INTO 单条数据测试表 VALUES (1, '张三', 30);
INSERT INTO 单条数据测试表 VALUES (2, '李四', 32);
commit;
-- 64.测试中文表名-中文字段,**更新数据**
UPDATE 单条数据测试表 SET 年龄 = 31 WHERE 记录ID = 1;
-- 65.测试中文表名-中文字段,删除数据
DELETE FROM 单条数据测试表 WHERE 记录ID = 1;
-- 66.测试中文表名,单个唯一建(INSERT,UPDATE,DELETE)
CREATE TABLE 单个唯一建 (unique_column VARCHAR2(50) UNIQUE,regular_column NUMBER);
-- 67.测试中文表名,单个唯一建-INSERT
INSERT INTO 单个唯一建 (unique_column, regular_column) VALUES ('UNQ_001', 100);
INSERT INTO 单个唯一建 (unique_column, regular_column) VALUES ('UNQ_002', 200);
COMMIT;
-- 68.测试中文表名,UPDATE
UPDATE 单个唯一建 SET regular_column = 456 WHERE unique_column = 'UNQ_001';
-- 69.测试中文表名,DELETE
DELETE FROM 单个唯一建 WHERE unique_column = 'UNQ_001';
-- 70.测试英文表名单条数据测试
CREATE TABLE one_data (employee_id NUMBER(6) PRIMARY KEY,first_name VARCHAR2(20) NOT NULL,age NUMBER(3),email VARCHAR2(50));
-- 71.测试英文表名插入单条数据
INSERT INTO one_data (employee_id, first_name, age, email) VALUES (1, 'John Doe', 30, 'john@example.com');
-- 72.测试英文表名更新单挑数据
UPDATE one_data SET email = 'john.doe@newexample.com', age = 31 WHERE employee_id = 1;
-- 73.测试英文表名删除单条数据
DELETE FROM one_data WHERE employee_id = 1;
-- 74.测试创建包含多种空值字段的表
CREATE TABLE user_profile (user_id VARCHAR2(20) PRIMARY KEY NOT NULL,phone VARCHAR2(20),birthdate DATE,salary NUMBER(10,2),memo CLOB,photo BLOB);
-- 75.测试显式插入NULL值
INSERT INTO user_profile VALUES ('U001', NULL, TO_DATE('1990-01-01','YYYY-MM-DD'), NULL, EMPTY_CLOB(), NULL);
INSERT INTO user_profile (user_id, salary) VALUES ('U002', 5000.00);
INSERT INTO user_profile VALUES ('U003', '13800138000', NULL, NULL, NULL, EMPTY_BLOB());
commit;
-- 76.测试将非空字段更新为空
 UPDATE user_profile SET phone = NULL WHERE user_id = 'U002';
-- 77.测试同时清空多字段
UPDATE user_profile SET birthdate = NULL, salary = NULL WHERE user_id = 'U003';
-- 78.测试删除电话号码为空的记录
DELETE FROM user_profile WHERE phone IS NULL;
-- 79.测试删除工资和生日均为空的记录
DELETE FROM user_profile WHERE salary IS NULL AND birthdate IS NULL;
-- 80.测试删除所有含空值的记录（任意字段为空)
DELETE FROM user_profile WHERE phone IS NULL OR birthdate IS NULL OR salary IS NULL OR memo IS NULL OR photo IS NULL;
-- 81.测试创建NUMBER类型
CREATE TABLE table_number (id NUMBER PRIMARY KEY,value NUMBER(10, 2));
-- 82.测试插入NUMBER类型的值
INSERT INTO table_number VALUES (1, 1234.56);
-- 83.测试创建字符类型char
CREATE TABLE table_char (id NUMBER PRIMARY KEY,value CHAR(10));
-- 84.测试插入字符类型char的值
INSERT INTO table_char VALUES (1, 'ABC');
-- 85.测试创建日期时间类型DATE表
CREATE TABLE table_date (id NUMBER PRIMARY KEY,value DATE);
-- 86.测试创建插入时间类型DATA的值
INSERT INTO table_date VALUES (1, TO_DATE('2023-10-01 12:30:45', 'YYYY-MM-DD HH24:MI:SS'));
-- 87.测试创建二进制类型RAW表
CREATE TABLE table_raw (id NUMBER PRIMARY KEY,value RAW(16));
-- 88.测试插入二进制类型RAW的值
INSERT INTO table_raw (id, value) VALUES (1, HEXTORAW('0123456789ABCDEFFEDCBA9876543210'));
-- 89.测试创建binary_float类型表
 CREATE TABLE tb_binary_float (id NUMBER PRIMARY KEY,value BINARY_FLOAT);
-- 90.测试插入binary_float类型单精度记录
INSERT INTO tb_binary_float VALUES (1, 123.456);
-- 91.测试创建binary_double类型表
CREATE TABLE tb_binary_double (id NUMBER PRIMARY KEY,value BINARY_DOUBLE);
-- 92.测试插入binary_double类型(双精度)记录
INSERT INTO tb_binary_double VALUES (1, 123456.789);
-- 93.测试创建VARCHAR2类型表
CREATE TABLE tb_varchar2 (id NUMBER PRIMARY KEY,value VARCHAR2(100));
-- 94.测试插入VARCHAR2类型记录
INSERT INTO tb_varchar2 VALUES (1, 'Oracle Database');
-- 95.测试创建NVARCHAR2类型表
CREATE TABLE tb_nvarchar2 (id NUMBER PRIMARY KEY,value NVARCHAR2(100));
-- 96.测试插入NVARCHAR2类型的记录
INSERT INTO tb_nvarchar2 VALUES (1, N'Unicode文本测试');
-- 97.测试创建时间戳TIMESTAMP表
CREATE TABLE tb_timestamp (id NUMBER PRIMARY KEY,value TIMESTAMP(6));
-- 98.测试插入TIMESTAMP的记录
INSERT INTO tb_timestamp VALUES (1, TO_TIMESTAMP('2025-12-01 12:30:45.123456', 'YYYY-MM-DD HH24:MI:SS.FF6'));
-- 99.测试创建时区时间戳TIMESTAMP WITH TIME ZONE测试表
CREATE TABLE tb_timestamp_tz (id NUMBER PRIMARY KEY,value TIMESTAMP(6) WITH TIME ZONE);
-- 100.测试插入时区时间戳TIMESTAMP WITH TIME ZONE的值
INSERT INTO tb_timestamp_tz VALUES (1,TO_TIMESTAMP_TZ('2023-10-01 12:30:45.123456 -12:00', 'YYYY-MM-DD HH24:MI:SS.FF6TZH:TZM'));
INSERT INTO tb_timestamp_tz VALUES (2,TO_TIMESTAMP_TZ('2023-10-01 12:30:45.123456 -10:00', 'YYYY-MM-DD HH24:MI:SS.FF6TZH:TZM'));
INSERT INTO tb_timestamp_tz VALUES (3,TO_TIMESTAMP_TZ('2023-10-01 12:30:45.123456 -8:00', 'YYYY-MM-DD HH24:MI:SS.FF6TZH:TZM'));
INSERT INTO tb_timestamp_tz VALUES (4,TO_TIMESTAMP_TZ('2023-10-01 12:30:45.123456 -6:00', 'YYYY-MM-DD HH24:MI:SS.FF6TZH:TZM'));
INSERT INTO tb_timestamp_tz VALUES (5,TO_TIMESTAMP_TZ('2023-10-01 12:30:45.123456 -4:00', 'YYYY-MM-DD HH24:MI:SS.FF6TZH:TZM'));
INSERT INTO tb_timestamp_tz VALUES (6,TO_TIMESTAMP_TZ('2023-10-01 12:30:45.123456 -2:00', 'YYYY-MM-DD HH24:MI:SS.FF6TZH:TZM'));
INSERT INTO tb_timestamp_tz VALUES (7,TO_TIMESTAMP_TZ('2023-10-01 12:30:45.123456 -0:00', 'YYYY-MM-DD HH24:MI:SS.FF6TZH:TZM'));
INSERT INTO tb_timestamp_tz VALUES (8,TO_TIMESTAMP_TZ('2023-10-01 12:30:45.123456 +2:00', 'YYYY-MM-DD HH24:MI:SS.FF6TZH:TZM'));
INSERT INTO tb_timestamp_tz VALUES (9,TO_TIMESTAMP_TZ('2023-10-01 12:30:45.123456 +4:00', 'YYYY-MM-DD HH24:MI:SS.FF6TZH:TZM'));
INSERT INTO tb_timestamp_tz VALUES (10,TO_TIMESTAMP_TZ('2023-10-01 12:30:45.123456 +6:00', 'YYYY-MM-DD HH24:MI:SS.FF6TZH:TZM'));
INSERT INTO tb_timestamp_tz VALUES (11,TO_TIMESTAMP_TZ('2023-10-01 12:30:45.123456 +8:00', 'YYYY-MM-DD HH24:MI:SS.FF6TZH:TZM'));
INSERT INTO tb_timestamp_tz VALUES (12,TO_TIMESTAMP_TZ('2023-10-01 12:30:45.123456 +10:00', 'YYYY-MM-DD HH24:MI:SS.FF6TZH:TZM'));
INSERT INTO tb_timestamp_tz VALUES (13,TO_TIMESTAMP_TZ('2023-10-01 12:30:45.123456 +12:00', 'YYYY-MM-DD HH24:MI:SS.FF6TZH:TZM'));
INSERT INTO tb_timestamp_tz VALUES (14,TO_TIMESTAMP_TZ('2023-10-01 12:30:45.123456 +14:00', 'YYYY-MM-DD HH24:MI:SS.FF6TZH:TZM'));
commit;
-- 101.测试创建时间间隔类型-年-月表
CREATE TABLE tb_interval_ym (id NUMBER PRIMARY KEY,duration INTERVAL YEAR TO MONTH);
-- 102.测试插入年月时间间隔类型的记录
INSERT INTO tb_interval_ym VALUES (1, INTERVAL '3-2' YEAR TO MONTH);
-- 103.测试创建时间间隔-天-秒表
CREATE TABLE tb_interval_ds (id NUMBER PRIMARY KEY,duration INTERVAL DAY TO SECOND);
-- 104.测试插入时间间隔-天-秒记录
INSERT INTO tb_interval_ds VALUES (1, INTERVAL '5 04:30:15.123' DAY TO SECOND);
-- 105.测试创建FLOAT和DOUBLE PRECISION类型的表
CREATE TABLE FLASH_GXX1 (number_column1 FLOAT);
-- 106.修改FLOAT和DOUBLE PRECISION列的类型
ALTER TABLE FLASH_GXX1 MODIFY (number_column1 FLOAT);
ALTER TABLE FLASH_GXX1 MODIFY (number_column1 DOUBLE PRECISION);
-- 107.插入值
INSERT INTO FLASH_GXX1 (number_column1) VALUES (123.45);
-- 108.测试DECIMAL的类型
CREATE TABLE financial_data11 (total_amount DECIMAL(15, 4));
-- 109.测试修改DECIMAL的类型
ALTER TABLE financial_data11 MODIFY (total_amount DECIMAL(20, 6));
-- 110.测试插入DECIMAL的值
INSERT INTO financial_data11 (total_amount) VALUES (1234567.8910);
-- 111.测试NUMERIC
CREATE TABLE numeric11_example1 (amount NUMERIC(12, 2));
-- 112.测试修改NUMERIC的类型
ALTER TABLE numeric11_example1 MODIFY (amount NUMERIC(15, 4));
-- 113.测试插入NUMERIC的值
INSERT INTO numeric11_example1 (amount) VALUES (12345.67);
-- 114.测试创建NOT NULL约束表
CREATE TABLE example123 (id NUMBER NOT NULL,name VARCHAR2(100) NOT NULL);
-- 115.测试创建UNIQUE约束表
CREATE TABLE "UNIQUE41" (id NUMBER UNIQUE,email VARCHAR2(100) UNIQUE);
-- 116.测试创建PRIMARY KEY约束表
CREATE TABLE "PRIMARY32" (id NUMBER PRIMARY KEY,name VARCHAR2(100));
-- 117.测试创建CHECK约束表
CREATE TABLE "CHECK31" (id NUMBER,age NUMBER CHECK (age >= 18));
-- 118.测试创建DEFAULT约束表
CREATE TABLE "DEFAULT33" (id NUMBER,created_date DATE DEFAULT SYSDATE);
-- 119.测试创建UNIQUE约束表
CREATE TABLE "multi_uk_test233" (id int,id2 int,val int,UNIQUE (id, id2));
-- 120.大对象类型BLOB测试
CREATE TABLE table_blob (id NUMBER PRIMARY KEY,value BLOB);
-- 121.插入大对象类型BLOB的值
INSERT INTO table_blob VALUES (1, EMPTY_BLOB());
-- 122.大对象类型CLOB测试
CREATE TABLE table_clob (id NUMBER PRIMARY KEY,value clob);
-- 123.测试插入大对象类型CLOB的值
INSERT INTO table_clob VALUES (1, TO_CLOB('This is a CLOB value'));
-- 124.CLOB字段边界值测试
CREATE TABLE CLOB_BOUNDARY_TEST (ID NUMBER PRIMARY KEY,CLOB_DATA CLOB,CREATE_TIME TIMESTAMP DEFAULT SYSTIMESTAMP);
-- 125.插入测试数据
INSERT INTO CLOB_BOUNDARY_TEST (ID, CLOB_DATA) VALUES (1, DBMS_RANDOM.STRING('X', 4000));
-- 126.CLOB测试
CREATE TABLE TEST_CLOB (id VARCHAR2(255) PRIMARY KEY,sm CLOB,sm2 CLOB);
-- 127.测试CLOB插入数据
INSERT INTO TEST_CLOB (id, sm, sm2) VALUES ('1', '初始数据 SM', '初始数据 SM2');
-- 128.NCLOB字符大对象
CREATE TABLE tb_nclob (id NUMBER PRIMARY KEY,value NCLOB);
-- 129.插入NCLOB值
INSERT INTO tb_nclob VALUES (1, CAST('NCLOB Unicode大文本' AS NCLOB));
-- 130.测试创建XML类型表
CREATE TABLE tb_xml (id NUMBER PRIMARY KEY,value XMLTYPE);
-- 131.测试插入XML类型的记录
INSERT INTO tb_xml VALUES (1,XMLTYPE('<employee><id>1</id><name>Bob</name></employee>'));
-- 132.测试创建ANYDATA类型表
CREATE TABLE tb_anydata (id NUMBER PRIMARY KEY,data ANYDATA);
-- 133.测试插入位置类型ANYDATA的记录
INSERT INTO tb_anydata VALUES (1, ANYDATA.ConvertNumber(100));
